﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;

namespace DAL
{
    public class HouseService
    {
        public static House UpdateHouse(House house)
        {
            string sql = string.Format("update House set "
                            + "HouseHolderId={0}, HouseTypeId={1}, LeaseModeId={2},StatusCodeId={3},"
                            + "Address=N'{4}', AdminReview=N'{5}', Duration={6}, ExpirationDate=N'{7}',"
                            + "IntakeTime=N'{8}', IsChecked={9}, IsRecommended={10}, OrderedTimes={11},"
                            + "Price={12},  RentedTimes={13}, Standard=N'{14}', RoomNumber={15},"
                            + "LargeImageURI=N'{16}', ThumbnailURI=N'{17}',PublishTime=N'{18}',AreaId={19},Rating={20} where Id={21}",
                            house.HouseHolderId, house.HouseTypeId, house.LeaseModeId, house.StatusCodeId,
                            house.Address, house.AdminReview, house.Duration, house.ExpirationDate,
                            house.IntakeTime, house.IsChecked, house.IsRecommended, house.OrderedTimes,
                            house.Price, house.RentedTimes, house.Standard, house.RoomNumber,
                            house.LargeImageUri, house.ThumbnailUri, house.PublishTime, house.AreaId,
                            house.Rating,
                            house.Id);
            DBHelper.ExecuteCommand(sql);
            return GetHouseById(house.Id);

        }
        public static House AddHouse(House house)
        {
            string sql = string.Format("insert into House("
                + "HouseHolderId, HouseTypeId, LeaseModeId, StatusCodeId, "
                + "Address, AdminReview, Duration, ExpirationDate,"
                + "IntakeTime, IsChecked, IsRecommended, OrderedTimes,"
                + "Price,  RentedTimes, Standard, RoomNumber,"
                + "LargeImageURI, ThumbnailURI,PublishTime, AreaId, Rating)"
                + " values("
                + "{0},{1},{2},{3}," + "N'{4}',N'{5}', {6}, '{7}',"
                + "'{8}',{9},{10},{11}," + "{12},{13},N'{14}',{15},"
                + "N'{16}',N'{17}','{18}',{19}, {20});"
                + "select @@IDENTITY;",
                house.HouseHolderId, house.HouseTypeId, house.LeaseModeId, house.StatusCodeId,
                house.Address, house.AdminReview, house.Duration, house.ExpirationDate,
                house.IntakeTime, house.IsChecked, house.IsRecommended, house.OrderedTimes,
                house.Price, house.RentedTimes, house.Standard, house.RoomNumber,
                house.LargeImageUri, house.ThumbnailUri, house.PublishTime, house.AreaId, house.Rating);
            int id = DBHelper.GetScalar(sql);
            return GetHouseById(id);
        }

        public static House GetHouseById(int id)
        {
            string sql = string.Format("select * from House where Id={0}", id);
            return GetHouseBySql(sql);
        }

        private static House GetHouseBySql(string sql)
        {
            List<House> list = GetHousesBySql(sql);
            if (list.Count() > 0)
                return list[0];
            return null;
        }
        public static List<House> GetAllHouses()
        {
            string sql = string.Format("select * from House");
            return GetHousesBySql(sql);
        }
        public static List<House> GetHousesBySql(string sql)
        {
            List<House> list = new List<House>();
            DataTable dt = DBHelper.GetDataSet(sql);
            foreach (DataRow row in dt.Rows)
            {
                House house = new House();
                house.Id = (int)row["Id"];
                house.HouseHolderId = (int)row["HouseHolderId"];
                house.HouseTypeId = (int)row["HouseTypeId"];
                house.LeaseModeId = (int)row["LeaseModeId"];
                house.StatusCodeId = (int)row["StatusCodeId"];
                house.Address = (string)row["Address"];
                house.AdminReview = (string)row["AdminReview"];
                house.Duration = (int)row["Duration"];
                house.ExpirationDate = DateTime.Parse(row["ExpirationDate"].ToString());
                house.IntakeTime = DateTime.Parse(row["IntakeTime"].ToString());
                house.IsChecked = (int)row["IsChecked"];
                house.IsRecommended = (int)row["IsRecommended"];
                house.OrderedTimes = (int)row["OrderedTimes"];
                house.Price = float.Parse(row["Price"].ToString());
                house.PublishTime = DateTime.Parse(row["PublishTime"].ToString());
                house.RentedTimes = (int)row["RentedTimes"];
                house.Standard = (string)row["Standard"];
                house.LargeImageUri = (string)row["LargeImageUri"];
                house.ThumbnailUri = (string)row["ThumbnailUri"];
                house.RoomNumber = (int)row["RoomNumber"];
                house.AreaId = (int)row["AreaId"];
                house.Rating = (int)row["Rating"];
                list.Add(house);
            }
            return list;
        }

        public static List<House> GetLatestHouses(int p)
        {
            string sql = string.Format("select top {0} * from House order by PublishTime desc", p);
            return GetHousesBySql(sql);
        }

        public static void DeleteHouse(int hId)
        {
            string sql = string.Format("delete from House where Id={0}", hId);
            DBHelper.ExecuteCommand(sql);
        }

        public static List<House> GetAllHouses(int UserId)
        {
            string sql = string.Format("select * from House where HouseHolderId={0} order by PublishTime desc", UserId);
            return GetHousesBySql(sql);
        }

        public static List<House> GetAllHouses(int UserId, int StatusCodeId)
        {
            string sql = string.Format("select * from House where HouseHolderId={0} and StatusCodeId={1} order by PublishTime desc", UserId, StatusCodeId);
            return GetHousesBySql(sql);
        }

        public static List<House> GetLatestHouses(int UserId, int count)
        {
            string sql = string.Format("select top {0} * from House where HouseHolderId={1} order by PublishTime desc", count, UserId);
            return GetHousesBySql(sql);
        }

        public static List<House> GetHousesChecked()
        {
            string sql = string.Format("select * from House where IsChecked=1 order by PublishTime desc");
            return GetHousesBySql(sql);
        }

        public static List<House> GetHousesNotChecked()
        {
            string sql = string.Format("select * from House where IsChecked=0 order by PublishTime desc");
            return GetHousesBySql(sql);
        }

        public static bool HasId(int id)
        {
            if (GetHouseById(id) == null)
                return false;
            else
                return true;
        }
        public static List<House> GetHousesChecked(int UserId)
        {
            string sql = string.Format("select * from House where IsChecked=1 and HouseHolderId={0} order by PublishTime desc", UserId);
            return GetHousesBySql(sql);
        }

        public static List<House> GetHousesNotChecked(int UserId)
        {
            string sql = string.Format("select * from House where IsChecked=0 and HouseHolderId={0} order by PublishTime desc", UserId);
            return GetHousesBySql(sql);
        }

        public static int Count()
        {
            string sql = string.Format("select count(*) from House");
            return DBHelper.GetScalar(sql);
        }
        public static List<House> GetHousesByStatus(int StatusCodeId)
        {
            string sql = string.Format("select * from House where StatusCodeId={0} order by PublishTime desc", StatusCodeId);
            return GetHousesBySql(sql);
        }
        public static DataTable getTopHouseByTime(int start, int size, string str)
        {

            string strSql = "select top " + (size) + " h.*,p.Name from House h,PersonalInformation p,HouseHolder hr "
            + "where h.HouseHolderId=hr.Id and hr.PersonalInformationId=p.Id "
            + "and h.Id not in ("
            + "select top " + (start - 1) + " h.Id from House h, HouseHolder hr,PersonalInformation p "
            + "where h.HouseHolderId=hr.Id and hr.PersonalInformationId=p.Id ) " + str;

            DataTable dtResult = DBHelper.GetDataSet(strSql);
            return dtResult;
        }
        public static int getHouseNum(string str)
        {

            string strSql = "select  h.*,p.Name from House h,PersonalInformation p,HouseHolder hr "
            + "where h.HouseHolderId=hr.Id and hr.PersonalInformationId=p.Id " + str;
            DataTable dtResult = DBHelper.GetDataSet(strSql);
            int count = dtResult.Rows.Count;
            return count;
        }
        ///根据 房源的ID寻找 房主的信息
        public static PersonalInformation getHouseHolderInfByHouseID(int HouseID)
        {

            string strsql = "select h.Id HouseHolderID, p.* from HouseHolder h,PersonalInformation p "
            + "where h.PersonalInformationId=p.Id "
            + "and h.Id=(select House.HouseHolderId from House where House.Id=" + HouseID + ")";
            List<PersonalInformation> list = new List<PersonalInformation>();
            DataTable dt = DBHelper.GetDataSet(strsql);
            foreach (DataRow row in dt.Rows)
            {
                PersonalInformation person = new PersonalInformation();
                person.Name = (string)row["Name"];
                person.Email = (string)row["Email"];
                person.Phone = (string)row["Phone"];
                list.Add(person);
            }
            if (list.Count() > 0)
            {

                return list[0];
            }
            return null;
        }
        public static List<House> GetRecommendedHouses(int p)
        {
            string sql = string.Format("select top {0} * from house where IsRecommended=1 order by PublishTime desc", p);
            return GetHousesBySql(sql);
        }
    }
}
